Skip to main content

Building API Queries with PostgreSQL

After Seeding the Database​

  • Once tables are created and seed data is inserted, the database contains sample entries.

  • Example tables now contain data:

    • users
    • user_profiles
    • projects
    • project_members

  • Example user table fields:

    • id
    • email
    • full_name
    • password_hash

  • Example profile table fields:

    • user_id
    • avatar_url
    • bio
    • phone

Purpose of Seeding​

  • Provides test data for development.

  • Allows testing of:

    • API queries
    • Joins
    • Filtering
    • Sorting

API Example: Get All Users

Goal​

Build an API:

GET /v1/users

This endpoint should:

  • Fetch all users
  • Include their profile information
  • Return everything in one API call

Reason:

  • Avoid making separate API calls for profile data.

Basic Query to Fetch Users

SELECT * FROM users;

Result​

Returns:

  • All rows from the users table.

However:

  • It does not include profile data.

Fetching User Profiles with Users

Problem​

User profile data exists in another table:

user_profiles

Relationship:

user_profiles.user_id → users.id

This is a foreign key relationship.

SQL JOIN

To combine user data with profile data, use a JOIN operation.

SQL Query Structure

Best practice when writing SQL:

Start with the FROM clause.

Reason:

  • Clearly defines where the data comes from.

Using Table Aliases

Example:

FROM users u

What is an Alias?​

An alias is a short name for a table.

Example:

users → u

Benefits:

  • Shorter queries
  • Easier readability

Joining Profile Data

Since profile data is stored separately:

user_profiles

We join the tables.

LEFT JOIN

LEFT JOIN user_profiles up
ON u.id = up.user_id

Join Condition​

users.id = user_profiles.user_id

Why LEFT JOIN Instead of INNER JOIN?

INNER JOIN​

  • Requires rows to exist in both tables.

If a user does not have a profile entry, the result:

user not returned

LEFT JOIN​

Returns:

  • All rows from the left table
  • Matching rows from the right table

If profile doesn't exist:

profile = NULL

Why this is important​

Users may not have edited their profiles yet.

Therefore:

  • Profile rows may not exist.

We still want to return the user.

Selecting the Required Data

After defining the data source:

FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id

We define what data we want.

Selecting User Fields

SELECT u.*

This returns:

  • All columns from the users table.

Embedding Profile Data as JSON

Instead of returning profile fields separately, convert them to JSON.

PostgreSQL function:

to_jsonb()

Query to Convert Profile Row to JSON

to_jsonb(up.*) AS profile

Explanation​

  • up.* → entire profile row
  • to_jsonb() → converts row to JSON
  • AS profile → creates a new column named profile

Complete Query

SELECT 
  u.*, 
  to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id;

Example Result

Each user row now contains:

id
email
full_name
password_hash
created_at
updated_at
profile (JSON)

Example:

{
  "id": "123",
  "email": "john@example.com",
  "full_name": "John Doe",
  "profile": {
    "bio": "Developer",
    "avatar_url": "...",
    "phone": "1234567890"
  }
}

Sorting Query Results

SQL does not guarantee ordering by default.

Therefore:

  • Always explicitly sort results.

Sorting by Created Date

Most APIs return newest entries first.

ORDER BY u.created_at DESC

Meaning​

  • Sort users by creation time
  • Latest users first

Final Query for GET /v1/users

SELECT 
  u.*, 
  to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id
ORDER BY u.created_at DESC;

API Data Flow

  1. Frontend calls:
GET /v1/users

  1. Backend executes SQL query.

  2. Database returns rows.

  3. Backend converts result to JSON.

  4. Response sent to frontend.

Serialization and Deserialization

Serialization​

Converting internal data structures into JSON.

Example:

object → JSON

Deserialization​

Converting database results into language-specific structures.

Example in Go:

SQL row → Go struct

Example Language Behavior

Node.js / JavaScript​

  • JSON handling is natural.
  • Serialization overhead is small.

Go​

Flow is typically:

DB row → struct → JSON → API response

API Example: Get Single User

Endpoint:

GET /v1/users/:userId

Example request:

/v1/users/abc123

Dynamic Parameters

userId is a dynamic parameter.

Backend extracts it from the URL.

Parameterized Queries

Instead of inserting the value directly into SQL, use parameterized queries.

Example concept:

SELECT * FROM users WHERE id = $1;

Where:

$1 = userId

Purpose of Parameterized Queries

Security​

Protects against SQL Injection attacks.

SQL Injection Example​

If queries are constructed using string concatenation:

SELECT * FROM users WHERE id = " + userInput

User might send:

1; DELETE FROM users

Result:

SELECT * FROM users WHERE id = 1; DELETE FROM users

This could delete all data.

How Parameterized Queries Prevent This​

Parameterized queries treat user input as data only, not SQL.

Example:

SELECT * FROM users WHERE id = $1;

If user sends:

DELETE FROM users

Database treats it as:

"DELETE FROM users"

Just a string.

No SQL execution occurs.

Query to Fetch One User with Profile

SELECT 
  u.*, 
  to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id
WHERE u.id = :userId
order by u.created_at desc;

Where:

:userId = 'UUID'

all the code is to be wrapped in single codes to be interpreted as string COMES from : /v1/users/:userId

Result

Returns:

  • One user
  • Their profile
  • As a single row

Backend Execution Flow

Typical backend architecture:

Router
 ↓
Handler
 ↓
Service
 ↓
Repository
 ↓
Database Query

Steps:

  1. Router receives request.
  2. Extracts userId.
  3. Passes it to service layer.
  4. Repository executes parameterized query.
  5. Database returns result.
  6. Backend serializes JSON.
  7. Response sent to frontend.